import pymysql
import pandas as pd

"""
省总委员表

姓名      性别      单位及职务       身份      所属ID        内部顺序

姓名两个字时，中间空两格
性别为男不用填，为女填女
单位及职务一串字符串
身份：常委，委员，经审常委，经审委员
内部顺序：内部排序
所属ID:（1省总领导，2太原，3大同，4朔州，5忻州，6吕梁，7晋中，8阳泉，9长治，10晋城，11临汾，12运城，13产业工会，14省总机关各部门及直属事业单位）

"""
save_file = r'C:\Users\Administrator\Desktop\省总委员名单.xls'

def all2xls():
    conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
    sql = "select * from 省总委员表 order by 所属ID ASC, 内部顺序 ASC"
    frame = pd.read_sql(sql,conn)
    frame.to_excel(save_file)

def sqlmess(mess = None,tablename= None,index= None,indexmess= None,order= None):
    if mess == None or mess == "":
        sql = "select * "
    else:
        sql = "select %s "%mess
    if tablename == None or tablename == "":
        return "table null"
    else:
        sql += "from %s" % tablename
    if not index == None and not indexmess == None\
            and not index == "" and not indexmess == "":

        sql += " where %s = '%s' "%(index, indexmess)
    else : return "index null"
    if not order == None and not order == "":
        sql += "order by %s ASC"%order
    print(sql)
    return sql
print(sqlmess(tablename= '省总委员表',index = '姓名', indexmess = '王  蕾'))

def mess2xls():
    conn = pymysql.connect("localhost", "root", "password", "contacts", charset='utf8')
    sql = sqlmess(tablename= '省总委员表',index = '姓名', indexmess = '光宇航')
    frame = pd.read_sql(sql, conn)
    frame.to_excel(save_file)

all2xls()